package com.situ.library.dao.impl;

import com.situ.library.dao.IBookDao;
import com.situ.library.pojo.entity.Book;
import com.situ.library.pojo.query.BookQuery;
import com.situ.library.pojo.vo.BookVO;
import com.situ.library.util.JDBCUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.util.ObjectUtils;

import java.util.ArrayList;
import java.util.List;

public class BookDaoImpl implements IBookDao {
    private JdbcTemplate template = new JdbcTemplate(JDBCUtils.getDataSource());

    @Override
    public List<BookVO> selectByPage(BookQuery bookQuery) {
        String sql = "SELECT b.id, b.`name`, b.photo, b.isbn, b.author, b.price, b.publisher, b.remain, b.count, b.time, t.id AS typeId, t.`name` AS typeName FROM book AS b INNER JOIN type AS t ON b.type_id = t.id";
        //构造一个集合存放所有的条件
        List<Object> queryList = new ArrayList<>();
        String queryName = bookQuery.getName();
        String queryIsbn = bookQuery.getIsbn();
        if (queryName != null && !queryName.equals("")) {
            sql += " and b.name like ?";
            queryList.add("%" + queryName + "%");
        }
        if (queryIsbn != null && !queryIsbn.equals("")) {
            sql += " and isbn like ?";
            queryList.add("%" + queryIsbn + "%");
        }
        if (!ObjectUtils.isEmpty(bookQuery.getBeginCreateTime()) &&
                !ObjectUtils.isEmpty(bookQuery.getEndCreateTime())) {
            sql += " and time between ? and ?";
            queryList.add(bookQuery.getBeginCreateTime());
            queryList.add(bookQuery.getEndCreateTime());
        }
        sql += " limit ?,?";
        int offset = (bookQuery.getPage() - 1) * bookQuery.getLimit();
        queryList.add(offset);
        queryList.add(bookQuery.getLimit());
        List<BookVO> list = template.query(sql,
                new BeanPropertyRowMapper<>(BookVO.class), queryList.toArray());
        return list;
    }

    @Override
    public int selectTotalCount(BookQuery bookQuery) {
        String sql = "select count(*) from book where 1=1";
        //构造一个集合存放所有的条件
        List<Object> queryList = new ArrayList<>();
        String queryName = bookQuery.getName();
        if (queryName != null && !queryName.equals("")) {
            sql += " and name like ?";
            queryList.add("%" + queryName + "%");
        }
//        Integer count = template.queryForObject(sql, Integer.class,queryList);
//        return count;
        return template.queryForObject(sql, Integer.class, queryList.toArray());
    }

    @Override
    public void deleteById(int id) {
        template.update("delete from book where id=?", id);
    }

    @Override
    public void add(Book book) {
        template.update("insert into book(name,type_id,photo,isbn,author,price,publisher,remain,count) values(?,?,?,?,?,?,?,?,?)",
                book.getName(), book.getTypeId(), book.getPhoto(),book.getIsbn(), book.getAuthor(), book.getPrice(),
                book.getPublisher(), book.getRemain(),book.getCount());
    }

    @Override
    public Book selectById(int id) {
        return template.queryForObject("select * from book where id=?",
                new BeanPropertyRowMapper<>(Book.class), id);
    }

    @Override
    public void update(Book book) {
        template.update("update book set name=?, type_id=?, photo=?,isbn=?,author=?," +
                        "price=?,publisher=?,remain=?,count=? where id=?",
                book.getName(), book.getTypeId(), book.getPhoto(), book.getIsbn(), book.getAuthor(), book.getPrice(),
                book.getPublisher(), book.getRemain(),book.getCount(),book.getId());
    }

    @Override
    public List<Book> selectAll() {
        return template.query("select * from book", new BeanPropertyRowMapper<>(Book.class));
    }
    @Override
    public void addExcel(Book book) {
        template.update("insert into book(id,name,type_id,photo,isbn,author,price,publisher,remain,count,time) values(?,?,?,?,?,?,?,?,?,?,?)",
                book.getId(), book.getName(), book.getPhoto(),book.getIsbn(),book.getAuthor(),book.getPrice(),book.getPublisher(),book.getRemain(), book.getCount(), book.getTime());
    }


}